<?php
defined('SYSPATH') or die('No direct access allowed.');
/**
 * 从数据库获取取预警信息
 * @author z
 * 
 */
class Service_warning{
	
	  //通过sku查询采购单
  static function get_purchase_by_sku($sku)
  {
    return DB::select(array('purchaseorder_details.status','details_status'),
    		'purchaseorder_details.*','purchaseorders.*')
    ->from('purchaseorder_details')
    ->join('purchaseorders')
    ->on('purchaseorder_details.purchaseorder_id','=','purchaseorders.id')
    ->where('purchaseorder_details.sku','=',$sku)
    ->execute('newerp')
    ->as_array();

  }

  //获取在途时间
  static function get_onway_time($po_id,$sku)
  {
    //采购单时间
    $purchaseorders = DB::select()
                    ->from('purchaseorders')
                    ->join('purchaseorder_details')
                    ->on('purchaseorders.id','=','purchaseorder_details.purchaseorder_id')
                    ->where('purchaseorders.id','=',$po_id)
                    ->where('purchaseorder_details.sku','=',$sku)
                    ->execute('newerp')
                    ->current();
    if(!$purchaseorders)
    {
      return;
    }
    
    //第一次入库时间
    $warehouseorders = DB::select()
                    ->from('warehouseorders')
                    ->where('purchaseorders_id','=',$po_id)
                    ->where('sku','=',$sku)
                    ->order_by('warehouse_date')
                    ->execute('newerp')
                    ->current();

    //采购时间
    $purchaseorders_time = $purchaseorders ? strtotime($purchaseorders['purchaseorder_date']) : NULL;
    //入库时间
    $warehouseorder_time = $warehouseorders ? strtotime($warehouseorders['warehouse_date']) : strtotime('now');


    //var_dump($purchaseorders_time);exit;
    if($purchaseorders_time and $warehouseorder_time)
    { 
    	return ($warehouseorder_time -  $purchaseorders_time)/(24*3600);
    }
  }

  //保存在途时间到供应商产品表
  static function save_delivery($wp_id)
  {
  	$onway_time = Service_warning::get_onway_time($wp_id['poid'], $wp_id['sku']);
  	$sql1 = "SELECT purchaseorder_date,supplier_id FROM purchaseorders WHERE id = ".$wp_id['poid']." LIMIT 1";
  	$purchase_date = DB::query(Database::SELECT, $sql1)->execute('newerp')->current();
  	if(!$purchase_date)
  	{
  		return ;
  	}
  	
  	$sql2 = "SELECT warehouse_date,sku FROM  warehouseorders 
  			WHERE purchaseorders_id = ".$wp_id['poid']." AND sku LIKE '".$wp_id['sku']."' 
  			ORDER BY  warehouse_date LIMIT 1";
  	$warehouse_date = DB::query(Database::SELECT, $sql1)->execute('newerp')->current();  	
  	if(!$warehouse_date)
  	{
  		return ;
  	}
  	$onway_time['iproviderID'] = $purchase_date['supplier_id'];
  	$onway_time['iproductID'] = $warehouse_date['sku'];
  	$onway_time['delivery']= (strtotime($warehouse_date['warehouse_date']) - strtotime($purchase_date['purchaseorder_date']))/(24*3600);
  	Service_warning::save_onway_time($onway_time);
  }
  //保存在途时间
  static function save_onway_time(array $onway_time)
  {
      if(!$onway_time 
        or !isset($onway_time['iproviderID']) 
        or !isset($onway_time['iproductID']) 
        or !isset($onway_time['delivery'])
        or $onway_time['delivery'] < 0)
      {
        return;
      }
      $onway_time['delivery'] = 10;
      $sql_string = "UPDATE erp_providerproducts SET delivery=(delivery+".$onway_time['delivery'].")/2";
      $sql_string .= " WHERE iproviderID=".'"'.$onway_time['iproviderID'].'"';
      $sql_string .= ' AND iproductID="'.$onway_time['iproductID'].'"';
		//var_dump($sql_string);exit;
      return DB::query(Database::UPDATE,$sql_string)->execute('readonlyerp');
  }

  //
//  static function get_provider_by_id($pv_id)
  static function get_provider_by_id($po_id)
  {
//  -----------------------------------kdw------------------------------------
//    $provider = DB::select()
//    ->from('erp_provider')
//    ->where('id','=',$pv_id)
//    ->execute('readonlyerp')
//    ->current();

    $provider=array();
    //根据订单号获取到联系人、支付、供应商id
    $relatedId= DB::query(Database::SELECT,'SELECT contact_id,pay_way_id,supplier_id,payaccountinfo_id
                    FROM purchasesupplier WHERE poid='.$po_id)->execute('newerp')->current();

    $purchase_order=DB::query(Database::SELECT,'SELECT pay_way,supplier_id FROM purchaseorders
                            WHERE id='.$po_id)->execute('newerp')->current();

    if($relatedId and isset($relatedId['id']) and $relatedId['id']>0){
        $supplier_id=$relatedId['supplier_id'];
        //该采购单创建时选择的供应商联系人
        $contacts=(array)Newerpclientapi::findContactById($relatedId['supplier_id']);
        //                var_dump($relatedId);exit();
        $provider['vPhone']=$contacts['phone'];
        $provider['vFax']=$contacts['fax'];
        $provider['vContact']=$contacts['contact'];
        $provider['vMobile']=$contacts['mobile'];

        //获取选择的供应商信息
        $supplier=(array)Newerpclientapi::getSuppliers(array('name,address'),array('id'=>"=$supplier_id"));
        $provider['vProviderName']=$supplier[0]['name'];
        $provider['vAddress']=$supplier[0]['address'];

        //获取付款帐号的信息
        $sql="SELECT bankname,bankaccountname,bankaccount FROM payaccountinfo WHERE id=".$relatedId['payaccountinfo_id'];
        $pay_indo=DB::query(Database::SELECT,$sql)->execute('newerp')->current();

        $provider['vAccount']=$pay_indo['bankaccount'];
        $provider['vEstablishBank']=$pay_indo['bankname'];
        $provider['vAccountname']=$pay_indo['bankaccountname'];
    }else{
        //获取选择的供应商信息
        $supplier=(array)Newerpclientapi::getSuppliers(array('name,address'),array('id'=>"=".$purchase_order['supplier_id']));
        $provider['vProviderName']=$supplier[0]['name'];
        $provider['vAddress']=$supplier[0]['address'];

        //查询供应商联系人
        $contacts=(array)Newerpclientapi::getSupplierContacts($purchase_order['supplier_id']);
        $provider['vPhone']=$contacts[0]['phone'];
        $provider['vFax']=$contacts[0]['fax'];
        $provider['vContact']=$contacts[0]['contact'];
        $provider['vMobile']=$contacts[0]['mobile'];

        //支付帐号信息
        $sql="SELECT bankname,bankaccountname,bankaccount FROM payaccountinfo WHERE id=
                            (SELECT payways_id FROM supplierpayaccount WHERE supplier_id=".$purchase_order['supplier_id']." LIMIT 0,1)";

        $pay_indo=DB::query(Database::SELECT,$sql)->execute('newerp')->current();
        $provider['vAccount']=$pay_indo['bankaccount'];
        $provider['vEstablishBank']=$pay_indo['bankname'];
        $provider['vAccountname']=$pay_indo['bankaccountname'];
    }

      //获取到供应商支付方式
      $pay_type=DB::query(Database::SELECT,'SELECT paywayname FROM payways WHERE id='.$purchase_order['pay_way'])
                       ->execute('newerp')->current();
      $provider['payment']=$pay_type['paywayname'];
//  -----------------------------------kdw-end--------------------------------
    return $provider;
  }

  //获取采购明细的状态
  static function get_purchase_details_status($code)
  {
		return DB::select('value')
		->from('statusdics')
		->where('statusdics.table','=','purchaseorder_details')
		->where('statusdics.colum_name','=','status')
		->where('number','=',$code)
		->execute('newerp')
		->current();
  }

  static function get_warning_tips($warning)
  {
      if(!isset($warning['best']))
      {
        return;
      }
  		if($warning['best'] < 1)
  		{
  			$tips = '<span style="color:red">紧急采购</span>';
  		}
  		else
  		{
  			$tips = '<span>普通</span>';
  		}

  		return $tips;
  }

  //获取历史采购记录
  static function get_purchase_history_by_sku($sku,$start,$end)
  {
      $count_all = DB::select(array(DB::expr('SUM(purchaseorder_details.quantity)'),'count'))
      ->from('purchaseorders')
      ->join('purchaseorder_details')
      ->on('purchaseorders.id','=','purchaseorder_details.purchaseorder_id')
      ->where('purchaseorders.purchaseorder_date','>=',$start)
      ->where('purchaseorders.purchaseorder_date','<',$end)
      ->where('purchaseorder_details.sku','=',$sku)
      ->group_by('purchaseorder_details.sku')
      ->execute('newerp')//var_dump($count_all);exit;
      ->current();

      return $count_all;
  }

     //获得全部的销售记录
    static function get_sale_history($start,$days,$sku)
    {
        $sale = array();
        $date = array();
        for($i = 0;$i <= $days; $i++)
        {
          $end =  date("Y-m-d",strtotime($start." + 1 days"));//
          $sale[] = service_daysale::count_daysale($sku,$start,$end);
          $date[] = $start;
          $start = $end;//var_dump($end);echo "<br/>";
        }
        return array('date'=>($date),'sale'=>($sale));
    }

    //历史库存记录
    static function get_inventory_history($start,$days,$sku)
    {
        //$inventory_history = new Service_Inventory();
        
        $inventory = array();
        $date = array();
        for($i = 0;$i <= $days; $i++)
        {
          $end =  date("Y-m-d",strtotime($start." + 1 days"));//
          //$inventory_history_get= $inventory_history->balances($start,$end,$sku);//var_dump($inventory[$start]);exit;
          $inventory_history_get = Service_warning::get_inventory_history_by_sku($sku, $start, $end);
          //var_dump($inventory_history_get);exit;
          $inventory[]= isset($inventory_history_get[0]['quantity'])?$inventory_history_get[0]['quantity']:0;
          $date[] = $start;
          $start = $end;//var_dump($end);echo "<br/>";

        }
        return array('inventory'=>$inventory,'date'=>$date);
    }

    //历史采购记录
    static function get_purchase_history($start,$days,$sku)
    {
        $purchase = array();
        $date = array();

        for ($i=0; $i < $days; $i++) { 
          $end =  date("Y-m-d",strtotime($start." + 1 days"));
          $purchase_count = Service_Warning::get_purchase_history_by_sku($sku,$start,$end);
          $purchase[] = isset($purchase_count['count'])?$purchase_count['count']:0;
          $date[] = $start;
          $start = $end;
        }

        return array('date'=>$date,'purchase'=>$purchase);
    }

    //查询库存
    static  function get_inventory_history_by_sku($sku,$start,$end)
    {
    	$tablename='inventories';
    	//下面三行代码是查看sku库存量，调用的是盘点的方法。
    	$pre= Pandian::getListInfoCondition(Pandian::getListPre($tablename),array('sku'=>$sku,'start_time'=>$start),true,$tablename);
    	$datas=Pandian::getList(1,0,$pre);
    	$datas=Pandian::excute_data($datas,$start);
    	return $datas;
    }
      //全部计税方式
  static function get_tax_ways()
  {
    return DB::select(array('number','id'),array('value','tax_way'))->from('statusdics')
      ->where('table','=','purchaseorder_detail')
      ->where('colum_name','=','tax_way')
      ->execute('newerp')
      ->as_array();
  }

  //全部的付款方式
  static function get_pay_ways()
  {
    return DB::select(array('id','id'),array('type','pay_way'))
    ->from('erp_pay_type')
    ->execute('readonlyerp')
    ->as_array();
  }

  //计算销量统计
  static function get_sale_ontime($sku,$date_end=NULL)
  {
     $sale = array();
      if($sku)
      {
        
        if (isset ($date_end) and trim ($date_end)) 
        {
          $date_end = date ( 'Ymd', strtotime ($date_end) );
        }
        else
        {
          $date_end = date('Ymd');
        }

        $sale['sku'] = $sku;
        $sale['date_end'] = $date_end;
        $days = array(7,14,28);
        foreach ($days as $key => $day) 
        {

          $date_start = date ( 'Ymd', strtotime ( $date_end. '-' .$day. 'day' ) );
          
          $sale['daysles'.$day] = service_daysale::count_daysale($sku,$date_start,$date_end,$day);//计算销量
        }

        $sale['daysles'] = $sale['daysles7']/7*0.5 + $sale['daysles14']/14*0.3+$sale['daysles28']/28*0.2;
      }

      return $sale;
  }
}